﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using PracticeAzure.Website.Shared;

namespace PracticeAzure.Website.Data
{
    public class CorrigendumAttachment
    {
        public long ID { get; set; }
        public long CorrigendumID { get; set; }
        public string URL { get; set; }
        public string ContentType { get; set; }
        public DateTime CreatedOn { get; set; }

        public static void BatchSave(IEnumerable<CorrigendumAttachment> attachments)
        {
            using (var conn = SqlHelperLite.CreateSqlConnection())
            {
                conn.Open();
                var tran = conn.BeginTransaction();
                try
                {
                    foreach (var attachment in attachments)
                    {
                        var cmd = conn.CreateCommand();
                        cmd.Transaction = tran;
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = "CreateCorrigendumAttachment";
                        cmd.Parameters.Add(new SqlParameter("@CorrigendumId", attachment.CorrigendumID));
                        cmd.Parameters.Add(new SqlParameter("@URL", attachment.URL));
                        cmd.Parameters.Add(new SqlParameter("@ContentType", attachment.ContentType));
                        cmd.Parameters.AddReturnValue();
                        cmd.ExecuteNonQuery();
                        attachment.ID = (long)(int)cmd.GetReturnValue();
                    }
                    tran.Commit();
                }
                catch
                {
                    tran.Rollback();
                    throw;
                }
            }
        }

        public static void BatchDelete(IEnumerable<long> ids)
        {
            using (var conn = SqlHelperLite.CreateSqlConnection())
            {
                conn.Open();
                var tran = conn.BeginTransaction();
                try
                {
                    foreach (var id in ids)
                    {
                        var cmd = conn.CreateCommand();
                        cmd.Transaction = tran;
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = "DeleteCorrigendumAttachment";
                        cmd.Parameters.Add(new SqlParameter("@id", id));
                        cmd.ExecuteNonQuery();
                    }
                    tran.Commit();
                }
                catch
                {
                    tran.Rollback();
                    throw;
                }
            }
        }

        public static IEnumerable<CorrigendumAttachment> List(long corrigendumId)
        {
            var attachments = new List<CorrigendumAttachment>();
            using (var conn = SqlHelperLite.CreateSqlConnection())
            {
                var cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "ListCorrigendumAttachments";
                cmd.Parameters.Add(new SqlParameter("@id", corrigendumId));
                conn.Open();
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        attachments.Add(new CorrigendumAttachment()
                        {
                            ID = (long)reader["ID"],
                            CorrigendumID = corrigendumId,
                            URL = (string)reader["URL"],
                            ContentType = reader["ContentType"] == DBNull.Value ? string.Empty : (string)reader["ContentType"],
                            CreatedOn = (DateTime)reader["CreatedOn"]
                        });
                    }
                }
            }
            return attachments;
        }
    }
}